package com.tanzeer.deliverytracker.dbhelper;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.SyncStateContract.Columns;

import com.tanzeer.deliverytracker.models.RowItem;
import com.tanzeer.deliverytracker.models.RowItem.ActivitySummary;
import com.tanzeer.deliverytracker.utility.ApplicationVars;
import com.tanzeer.deliverytracker.utility.ApplicationVars.Subscription;

public class RelsellDBOpenHelper extends SQLiteOpenHelper {

	private static String Databasename = "tracking";
	private static int Databasevertion = 8;
	private static String itemSummaryTable ="tracks_itemsummary";
	private static String activitySummaryTable ="activity_summary";
	public static String coloumTrackNo = "trackno";
	public static String coloumCategory = "category";
	public static String coloumStatus = "status";
	public static String coloumActivity = "activity";
	public static String coloumOrigin = "origin";
	public static String coloumDestination = "destination";
	public static String coloumLocation = "location";
	public static String coloumRemarks = "remarks";
	public static String coloumLocalDateTime = "local_date_time";
	public static String coloumId = "_id";
	public static String coloumCompleted = "completed";
	public static String coloumtrackId = "trackid";
	public static String coloumtrackDataId = "trackdataid";
	public static String coloumTrackName = "trackname";
	public static String createItemSummaryTableQuery = "create table if not exists "
			+ itemSummaryTable + " (" + coloumId
			+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + coloumTrackNo + " TEXT, " + coloumCategory + " TEXT, "+ coloumTrackName + " TEXT, "
			+ coloumCompleted + " TEXT, " + coloumOrigin + " TEXT, "+ coloumDestination + " TEXT, " + coloumStatus + " TEXT);";
	
	public static String createActivitySummaryTableQuery= "create table if not exists "
			+ activitySummaryTable + " (" + coloumId
			+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + coloumtrackId + " TEXT, "+ coloumtrackDataId + " TEXT, " + coloumLocation+ " TEXT, "
			+ coloumRemarks+ " TEXT, " + coloumLocalDateTime+ " TEXT, " + coloumActivity + " TEXT );";

	
	private String[] itemSummarytableColums = { coloumTrackNo, coloumCategory,coloumId,
			coloumStatus, coloumOrigin, coloumDestination,coloumTrackName,coloumCompleted };
	private String[] activitySummarytableColums = { coloumtrackDataId,coloumtrackId,coloumLocalDateTime,coloumActivity,coloumLocation,coloumRemarks };

	public RelsellDBOpenHelper(Context context) {
		super(context, Databasename, null, Databasevertion);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		System.out.println("Creating database");
		db.execSQL(createItemSummaryTableQuery);   
		db.execSQL(createActivitySummaryTableQuery);   
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		onCreate(db);
	}
	
	public void dropDBTables() {
		SQLiteDatabase db = this.getWritableDatabase();
		db.execSQL("DROP TABLE IF EXISTS "+itemSummaryTable);
		db.execSQL("DROP TABLE IF EXISTS "+activitySummaryTable);
	}
	
	public void getItemSummaryList(ArrayList<RowItem> list,String type,String categoryName) {
		RowItem rI = null;
		Cursor c = null;
		SQLiteDatabase db = null;
		try {
			db = this.getReadableDatabase();
			String selection = coloumCompleted;

			if (type.equalsIgnoreCase("Active")) {
				c = db.query(itemSummaryTable, itemSummarytableColums,
						selection + "=?", new String[] { "0" }, null, null,
						null);
			} else if (type.equalsIgnoreCase("Completed")) {
				c = db.query(itemSummaryTable, itemSummarytableColums,
						selection + "=?", new String[] { "1" }, null, null,
						null);
			} else if (type.equalsIgnoreCase("Category")) {
				String where1 = coloumCategory;
				String where2 = coloumCompleted;
				c = db.query(itemSummaryTable, itemSummarytableColums, where1
						+ "=? AND " + where2 + "=?", new String[] {
						categoryName, "0" }, null, null, null);
			}

			int countVar = 0;
			if (ApplicationVars.getInstance().isSubscribed()) {
				Subscription scr = ApplicationVars.getInstance()
						.getSubscriptionObject();
				countVar = scr.getSubscrtionTypeInteger();
			}
			while (c.moveToNext() && countVar != 0) {
				String track = c.getString(c.getColumnIndex(coloumTrackNo));
				String category = c.getString(c.getColumnIndex(coloumCategory));
				String id = c.getString(c.getColumnIndex(coloumId));
				String status = c.getString(c.getColumnIndex(coloumStatus));
				String origin = c.getString(c.getColumnIndex(coloumOrigin));
				String destination = c.getString(c
						.getColumnIndex(coloumDestination));
				String completed = c.getString(c
						.getColumnIndex(coloumCompleted));
				String trackName = c.getString(c
						.getColumnIndex(coloumTrackName));

				rI = new RowItem(track, category, status, origin, destination,
						id, completed, trackName);
				list.add(rI);
				countVar--;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (c != null) {
				c.close();
			}
			if (db != null) {
				db.close();
			}
		}
		
	}
	
	public void getItemSummaryList(ArrayList<RowItem> list,String trackId) {
		RowItem rI = null;
		SQLiteDatabase db = this.getReadableDatabase();

		Cursor c = null;
		c = db.query(itemSummaryTable, itemSummarytableColums, coloumId+"=?",new String[]{trackId}, null, null,null);

		int countVar = 0;
		if(ApplicationVars.getInstance().isSubscribed()){
			Subscription scr = ApplicationVars.getInstance().getSubscriptionObject();
			countVar = scr.getSubscrtionTypeInteger();
		}
		while (c.moveToNext() && countVar != 0) {
			String track = c.getString(c.getColumnIndex(coloumTrackNo));
			String category = c.getString(c.getColumnIndex(coloumCategory));
			String id = c.getString(c.getColumnIndex(coloumId));
			String status = c.getString(c.getColumnIndex(coloumStatus));
			String origin = c.getString(c.getColumnIndex(coloumOrigin));
			String destination = c.getString(c
					.getColumnIndex(coloumDestination));
			String completed = c.getString(c
					.getColumnIndex(coloumCompleted));
			String trackName = c.getString(c
					.getColumnIndex(coloumTrackName));
			
			rI = new RowItem(track,category, status, origin,
					destination,id,completed,trackName);
			list.add(rI);
			countVar--;
		}
		c.close();
		db.close();
	}
	
	public void getItemSummaryListForRefresh(ArrayList<RowItem> list) {
		RowItem rI = null;
		SQLiteDatabase db = this.getReadableDatabase();

		Cursor c = null;
		
		c = db.query(itemSummaryTable, itemSummarytableColums,null,null,null, null,
					null);

		int countVar = 0;
		if(ApplicationVars.getInstance().isSubscribed()){
			Subscription scr = ApplicationVars.getInstance().getSubscriptionObject();
			countVar = scr.getSubscrtionTypeInteger();
		}
		while (c.moveToNext() && countVar != 0) {
			String track = c.getString(c.getColumnIndex(coloumTrackNo));
			String category = c.getString(c.getColumnIndex(coloumCategory));
			String id = c.getString(c.getColumnIndex(coloumId));
			String status = c.getString(c.getColumnIndex(coloumStatus));
			String origin = c.getString(c.getColumnIndex(coloumOrigin));
			String destination = c.getString(c
					.getColumnIndex(coloumDestination));
			String completed = c.getString(c
					.getColumnIndex(coloumCompleted));
			String trackName = c.getString(c
					.getColumnIndex(coloumTrackName));
			
			rI = new RowItem(track,category, status, origin,
					destination,id,completed,trackName);
			list.add(rI);
			countVar--;
		}
		c.close();
		db.close();
	}
	
	public ArrayList<String> getListDataForNavigationDrawer() {
		ArrayList<String> list = new ArrayList<String>();
		this.getWritableDatabase();
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor c = db.query(itemSummaryTable, itemSummarytableColums, null,
				null, null, null, null);
		while (c.moveToNext()) {
			String categoryName = c.getString(c.getColumnIndex(coloumCategory));
            String completed = c.getString(c.getColumnIndex(coloumCompleted));
            
			if (!list.contains(categoryName) && completed.equalsIgnoreCase("0")) {
				list.add(categoryName);
			}	
		}
		c.close();
		db.close();
		return list;
	}


	public int saveDelivery(String description,String tracknumber,
			String category,String status) {
		
		try {
		
			SQLiteDatabase db = this.getReadableDatabase();
			db.close();
			db = this.getWritableDatabase();
			ContentValues values = new ContentValues();
			values.put(coloumTrackNo, tracknumber);
			values.put(coloumCategory, category);
			values.put(coloumStatus, status);
			values.put(coloumTrackName, description);
			values.put(coloumCompleted, "0");
			db.insert(itemSummaryTable, null, values);
			db.close();
			return 1;
			
		} catch (Exception e) {
			e.printStackTrace();
			return -100;
		}
	}
	
	public int saveActivitySummaryForItem(String trackingDataId,String trackingNo,String localdateTime,String activity,
			String location,String remarks) {
		
		try {
		
			SQLiteDatabase db = this.getReadableDatabase();
			db.close();
			db = this.getWritableDatabase();
			ContentValues values = new ContentValues();
			values.put(coloumtrackDataId,trackingDataId);
			values.put(coloumtrackId, trackingNo);
			values.put(coloumLocalDateTime, localdateTime);
			values.put(coloumActivity,activity);
			values.put(coloumLocation,location);
			values.put(coloumRemarks,remarks);
			db.insert(activitySummaryTable, null, values);
			db.close();
			return 1;
			
		} catch (Exception e) {
			e.printStackTrace();
			return -100;
		}
	}
	
	public int updateItemDataForTrackNo(String completed,String status,String destination,String origin,String trackno) {
		int res = -1;
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		if(completed != null)
		values.put(coloumCompleted,completed);
		values.put(coloumStatus,status);
		values.put(coloumDestination,destination);
		values.put(coloumOrigin,origin);
		res = db.update(itemSummaryTable, values, coloumTrackNo + "=?", new String[]{trackno});
		return res;
	}

	public void updatePackageDelivery(RowItem rItem,boolean markCompleted) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(coloumStatus,"DELIVERED");
		if (markCompleted) {
		    values.put(coloumCompleted, "1");
		} else {
			values.put(coloumCompleted, "0");
		}
		db.update(itemSummaryTable, values, coloumId + "=?", new String[]{rItem.itemSummary._id});
		db.close();
	}
	
	public int removeTrackNo(RowItem rItem) {
		SQLiteDatabase db = this.getWritableDatabase();
		
		int res = db.delete(itemSummaryTable,coloumId + "=?",new String[]{rItem.itemSummary._id});
		db.close();
		return res;
	}
	
	public void getActivitySummaryDataForTrack(String trackDataId,String trackId,ArrayList<RowItem> list) {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor c = db.query(activitySummaryTable, activitySummarytableColums, coloumtrackId+"=? AND "+coloumtrackDataId+"=?", new String[]{trackId,trackDataId}, null, null,
				null);
		RowItem  rItem = null;
		while (c.moveToNext()) {
			String trackIdData = c.getString(c.getColumnIndex(coloumtrackId));
			String dateTime = c.getString(c.getColumnIndex(coloumLocalDateTime));
			String activity = c.getString(c.getColumnIndex(coloumActivity));
			String location = c.getString(c.getColumnIndex(coloumLocation));
			String remarks = c.getString(c.getColumnIndex(coloumRemarks));
			rItem = new RowItem(trackIdData,activity,location,remarks,dateTime);
			list.add(rItem);
		}
		c.close();
		db.close();
	}
	
	public boolean checkDBPresence(Context context) {

		String database = context.getDatabasePath(Databasename).toString();

		SQLiteDatabase checkDB = null;
		try {
			checkDB = SQLiteDatabase.openDatabase(database, null,
					SQLiteDatabase.OPEN_READONLY);
			checkDB.close();
		} catch (SQLiteException e) {
			e.printStackTrace();
		}
		return checkDB != null ? true : false;
	}

	public void openDb() {
		this.getWritableDatabase();
	}

	public int updatePackageDelivery(String tracknumber, String description, String category,
			String status) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(coloumTrackName,description);
		values.put(coloumCategory,category);
		values.put(coloumStatus,status);
		values.put(coloumCompleted, "0");
		db.update(itemSummaryTable, values, coloumTrackNo + "=?", new String[]{tracknumber});
		db.close();
		return 0;
	}
}
